﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace libConn
{
    public static class AccdbConn
    {
        private static OleDbConnection rs;
        private static string strConnection =
            "Provider=Microsoft.ACE.OLEDB.12.0;jet oledb:database password=;Data Source=";

        public static void conn(string str, ref string e)
        {
            if (str.Length == 0 || str == null)
            {
                e = "Database file path error.";
                return;
            }

            try
            {
                rs = new OleDbConnection(strConnection + str);
                rs.Open();
            }
            catch (Exception es)
            {
                e = es.Message;
            }
        }

        public static void disconn(ref string e)
        {
            try
            {
                rs.Close();
            }
            catch (Exception es)
            {
                e = es.Message;
            }
            rs = null;
        }

        public static void exec(string str, ref string e)
        {
            long result = 0;
            if (str.Length == 0 || str == null)
            {
                e = "Database operating instruction error.";
                return;
            }

            OleDbTransaction ts = rs.BeginTransaction();

            try
            {
                OleDbCommand command = new OleDbCommand(@str, rs, ts);
                result = command.ExecuteNonQuery();
                ts.Commit();
            }
            catch (Exception es)
            {
                ts.Rollback();
                e = es.Message;
                return;
            }

            if (result == 0)
                e = "Database operation failed.";
        }

        public static long exec_ins(string str, ref string e)
        {
            long result = 0;
            if (str.Length == 0 || str == null)
            {
                e = "String missing database operations.";
                return 0;
            }

            OleDbTransaction ts = rs.BeginTransaction();
            try
            {
                OleDbCommand command = new OleDbCommand(str, rs, ts);
                result = command.ExecuteNonQuery();
                ts.Commit();
                command.CommandText = "SELECT @@identity AS id";
                result = Convert.ToInt32(command.ExecuteScalar());
            }
            catch (Exception es)
            {
                ts.Rollback();
                e = es.Message;
            }

            return result;
        }

        public static DataTable query(string str, ref string e)
        {
            if (str.Length == 0 || str == null)
            {
                e = "String missing database operations";
                return null;
            }

            OleDbDataAdapter oleDA;
            DataSet oleDS = new DataSet();
            DataTable oleDT;

            try
            {
                oleDA = new OleDbDataAdapter(str, libConn.AccdbConn.rs);
                oleDA.Fill(oleDS, "Alert");
                oleDT = oleDS.Tables["Alert"];
            }
            catch (Exception es)
            {
                e = es.Message;
                return null;
            }

            return oleDT;
        }

        public static bool insert(
            string queryString, string tableName,
            int ColumnCount, DataRow datarow, ref string e)
        {
            try
            {
                e = "";

                DataSet dataSet = new DataSet();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand(queryString, AccdbConn.rs);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

                adapter.FillSchema(dataSet, SchemaType.Mapped);
                adapter.Fill(dataSet, tableName);

                DataTable table = dataSet.Tables[tableName];
                DataRow dr = table.NewRow();

                for (int i = 0; i < ColumnCount; i++)
                {
                    dr[i] = datarow[i];
                }

                table.Rows.Add(dr);

                adapter.Update(dataSet, tableName);

                return true;
            }
            catch (Exception ex)
            {
                e = ex.Message;
                return false;
            }
        }

        public static bool update(
            string queryString, string tableName,
            int ColumnCount, DataRow datarow, ref string e)
        {
            try
            {
                e = "";

                DataSet dataSet = new DataSet();

                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand(queryString, AccdbConn.rs);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

                adapter.Fill(dataSet, tableName);

                DataTable table = dataSet.Tables[tableName];
                DataRow dr = table.Rows[0];

                for (int i = 0; i < ColumnCount; i++)
                {
                    dr[i] = datarow[i];
                }

                adapter.Update(dataSet, tableName);

                return true;
            }
            catch (Exception ex)
            {
                e = ex.Message;
                return false;
            }
        }

        public static DataTable getTable(string queryString, string tableName, ref string e)
        {
            try
            {
                e = "";

                DataSet dataSet = new DataSet();

                OleDbDataAdapter adapter = new OleDbDataAdapter();
                adapter.SelectCommand = new OleDbCommand(queryString, AccdbConn.rs);
                OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);

                adapter.Fill(dataSet, tableName);

                DataTable table = dataSet.Tables[tableName].Copy();

                return table;
            }
            catch (Exception ex)
            {
                e = ex.Message;
                return new DataTable();
            }
        }
    }
}
